﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Reflection;
using System.IO;
using System.Diagnostics;
using AdomdClient = Microsoft.AnalysisServices.AdomdClient;

namespace template_dll
{
    public partial class ChildForm : Form
    {
        public ChildForm()
        {
            InitializeComponent();
        }

        private const string namechcol = "Флаг";
        private SqlConnection conn = new SqlConnection();
        private DataTable dt_rep;
        public DataTable dt_pharm;

        private delegate void Deleg();
        private Deleg del;
        private struct date_str { public DateTime dt; public string dtname; }
        private date_str[] dt_str = new date_str[] { };
        private struct kkm_str { public string km; public string pharm; }
        private kkm_str[] km_str = new kkm_str[] { };
        private DateTime date_beg;
        private DateTime date_end;

        public static bool DisplayForm(System.Windows.Forms.Form theMDIParent)
        {
            ChildForm f = new ChildForm();
            f.MdiParent = theMDIParent;
            f.WindowState = FormWindowState.Maximized;
            f.Show();
            return true;
        }

        private string GetServ(string filename)
        {
            string str = "";
            using (StreamReader streamReader = new StreamReader(filename))
            {
                while (!streamReader.EndOfStream)
                {
                    str += streamReader.ReadLine();
                }
            }
            return str;
        }

        private void ChildForm_Load(object sender, EventArgs e)
        {
            conn = new SqlConnection(@"Server=" + GetServ("serv.txt") + ";Database=PharmOLAPDW;Integrated Security=True");
            //SqlCommand getdate = conn.CreateCommand();
            //getdate.CommandText = @"pas_MonthSel";
            //getdate.CommandType = CommandType.StoredProcedure;
            //DataTable date = new DataTable();
            //if (conn.State == ConnectionState.Closed) conn.Open();
            //date.Load(getdate.ExecuteReader());
            //if (date.Rows.Count > 0)
            //{
            //    foreach (DataRow dr in date.Rows)
            //    {
            //        Array.Resize(ref dt_str, dt_str.Length + 1);
            //        dt_str[dt_str.Length - 1].dt = Convert.ToDateTime(dr[0]);
            //        dt_str[dt_str.Length - 1].dtname = dr[1].ToString();
            //        comboBox1.Items.Add(dr[1].ToString());
            //        if (DateTime.Now.Date.Month == Convert.ToDateTime(dr[0]).Month)
            //            comboBox1.SelectedIndex = comboBox1.Items.Count - 1;
            //    }

            //}
            //date = null;
            //getdate = null;

            comboBox3.SelectedIndex = 0;

            //- по умолчанию - даты вчерашняя, и минус 7 дней
            DateTime dd = DateTime.Today;
            date_end = dd.AddDays(-1);
            date_beg = dd.AddDays(-8);
            dateTimePicker1.Value = date_beg;
            dateTimePicker2.Value = date_end;
            
            //- Загрузка данных КМ
            SqlCommand getkm = conn.CreateCommand();
            getkm.CommandText = @"pas_KM_sel";
            getkm.CommandType = CommandType.StoredProcedure;
            DataTable km = new DataTable();
            if (conn.State == ConnectionState.Closed) conn.Open();
                km.Load(getkm.ExecuteReader());
            if (km.Rows.Count > 0)
            {
                //comboBox3.DataSource = km;
                //comboBox3.DisplayMember = "NameKm";
                //comboBox3.ValueMember = "IDKm";
                comboBox4.DataSource = km;
                comboBox4.DisplayMember = "FullName";
                comboBox4.ValueMember = "OtdID";


                //foreach (DataRow dr in km.Rows)
                //{
                //    Array.Resize(ref km_str, km_str.Length + 1);
                //    km_str[km_str.Length - 1].km = dr[1].ToString();
                //    km_str[km_str.Length - 1].pharm = dr[3].ToString();
                //    //comboBox3.Items.Add(dr[1].ToString());
                //    comboBox4.Items.Add(dr[3].ToString());
                //    comboBox3.SelectedIndex = 0;
                //    comboBox4.SelectedIndex = 0;
                //}
            }
            
        }

        private void StartCreateRep(object sender, EventArgs e)
        {
            dataGridView1.DataSource = null;
            dt_rep.Clear();
            del = new Deleg(CreateRep);
            this.Text += " Формируется";
            IAsyncResult asyncRes = del.BeginInvoke(new AsyncCallback(CallBackFunc), null); //
            panel1.Enabled = false;
            dataGridView1.Enabled = false;
        }

        private void CallBackFunc(IAsyncResult aRes)
        {
            del.EndInvoke(aRes);
            panel1.Invoke((Func<bool>)(() => panel1.Enabled = true));
            dataGridView1.Invoke((Func<bool>)(() => dataGridView1.Enabled = true));
            this.Invoke((Func<string>)(() => this.Text = this.Text.Replace(" Формируется", "")));
            dataGridView1.Invoke((Func<object>)(() => dataGridView1.DataSource = dt_rep.DefaultView));
        }

        private void CreateRep()
        {
            if (conn.State == ConnectionState.Closed) conn.Open();
            try
            {
                dt_rep.Load(RepProc(conn));
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                conn.Close();
            }

        }

        private SqlDataReader RepProc(SqlConnection sqlcon)//метод для вызова хранимки
        {
            SqlCommand sqlcom = sqlcon.CreateCommand();
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = @"sp_name"; //имя хранимой процедуры
            sqlcom.CommandTimeout = 300;
            return sqlcom.ExecuteReader();
        }

        private void ExpToExcel(object sender, EventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.FileName = "csv-file";
            sfd.Filter = "Файл CSV | *.csv";
            if (sfd.ShowDialog() == DialogResult.OK)
            {   
                SaveDGVToCSVfile(sfd.FileName.ToString(), dataGridView1, true);
            }
        }

        public static bool SaveDGVToCSVfile(string filename, DataGridView table, bool openInExcel)
        {
            try
            {
                StreamWriter sw = new StreamWriter(filename, false, Encoding.Unicode);

                List<int> col_n = new List<int>();
                foreach (DataGridViewColumn col in table.Columns)
                    if (col.Visible)
                    {
                        sw.Write(col.HeaderText + "\t");
                        col_n.Add(col.Index);
                    }
                sw.WriteLine();
                int x = table.RowCount;
                if (table.AllowUserToAddRows) x--;

                for (int i = 0; i < x; i++)
                {
                    for (int y = 0; y < col_n.Count; y++)
                        sw.Write(table[col_n[y], i].Value + "\t");
                    sw.Write(" \r\n");
                }
                sw.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return false;
            }

            if (openInExcel)
            {
                try
                {
                    Process newProc = Process.Start("excel.exe", "\"" + filename + "\"");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                }
            }
            return true;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //- Обновление таблиц
            SqlStorProc.sp_params[] par = new SqlStorProc.sp_params[4];
            par[0].name = "@km";
            par[0].value = comboBox3.SelectedIndex + 1000;
            par[1].name = "@days_b";
            par[1].value = date_beg;
            par[2].name = "@days_e";
            par[2].value = date_end;
            par[3].name = "@pharm";
            par[3].value = dt_pharm;
            dt_rep = (new SqlStorProc()).SqlProcExecWithQueryOneTable(conn, "pas_Report_KM", 3000, par);
            if (dt_rep.Rows.Count > 0)
            {
                MessageBox.Show("Данные рассчитаны");
            }
            else
            {
                MessageBox.Show("Ошибка загрузки данных");
            }
            dataGridView1.DataSource = dt_rep.DefaultView;
            conn.Close();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            //- Сформировать куб
            AdomdClient.AdomdConnection cnn = null;
            try
            {
                cnn = new AdomdClient.AdomdConnection("Provider=MSOLAP;  DataSource=sdata2;Initial Catalog=Report_KM");
                cnn.Open();

                AdomdClient.AdomdCommand cmd = cnn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText =
                 @"<Batch xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine"">" +
                  @"<Process xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:ddl2=""http://schemas.microsoft.com/analysisservices/2003/engine/2"" xmlns:ddl2_2=""http://schemas.microsoft.com/analysisservices/2003/engine/2/2"" xmlns:ddl100_100=""http://schemas.microsoft.com/analysisservices/2008/engine/100/100"" xmlns:ddl200=""http://schemas.microsoft.com/analysisservices/2010/engine/200"" xmlns:ddl200_200=""http://schemas.microsoft.com/analysisservices/2010/engine/200/200"" xmlns:ddl300=""http://schemas.microsoft.com/analysisservices/2011/engine/300"" xmlns:ddl300_300=""http://schemas.microsoft.com/analysisservices/2011/engine/300/300"">" +
                    @"<Object>" +
                      @"<DatabaseID>Report_KM</DatabaseID>" +
                    @"</Object>" +
                    @"<Type>ProcessFull</Type>" +
                    @"<WriteBackTableCreation>UseExisting</WriteBackTableCreation>" +
                  @"</Process>" +
                 @"</Batch>";

                cmd.ExecuteNonQuery();
                MessageBox.Show("Куб сформирован");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                cnn.Close();
            }
        }

        private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
        {
            //- Дата начала периода
            date_beg = dateTimePicker1.Value;
        }

        private void dateTimePicker2_ValueChanged(object sender, EventArgs e)
        {
            //- Дата окончания периода
            date_end = dateTimePicker2.Value;
        }

        private void button3_Click(object sender, EventArgs e)
        {
            //- Фильтр по аптекам
            try
            {
                PharmFilter.PharmFilter f3 = new PharmFilter.PharmFilter();

                if (dt_pharm == null)
                {
                    SqlCommand myCommand = conn.CreateCommand();
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.CommandText = @"pas_PharmSel";
                    if (conn.State == ConnectionState.Closed) conn.Open();
                    dt_pharm = new DataTable();
                    dt_pharm.Load(myCommand.ExecuteReader());
                    foreach (DataColumn dc in dt_pharm.Columns)
                    {
                        if (dc.ColumnName != namechcol) dc.ReadOnly = true;
                        else dc.ReadOnly = false;
                    }
                    conn.Close();
                }
                f3.namechcol = namechcol;
                f3.Text = "Фильтр по аптекам";
                f3.sortcoumn = "Аптека";
                f3.dataGridView1.DataSource = dt_pharm.DefaultView;
                f3.ShowDialog();
                listBox1.Items.Clear();
                foreach (DataRow dr in dt_pharm.Rows)
                {
                    if (Convert.ToBoolean(dr[namechcol]) == true) listBox1.Items.Add(dr[f3.sortcoumn]);
                }
                if (listBox1.Items.Count == 0) listBox1.Items.Add("(Все аптеки)");
                f3.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

        }

    }
}
